PD Collection CD 1/PD Collection CD 1.iso/data/powerbas/Docs/Calculate

Text File | 1995-08-21 | 11KB | 246 lines

Ch 6 − Performing Calculations

There are two mechanisms for performing calculations in Powerbase:−

•       Self-calculating fields which automatically calculate and display a
value derived from other fields.

•       Arithmetic performed on a column in a report.

Self-calculating fields are those which are of Calculated or Composite
types. Calculated fields display a numeric result derived from other fields
in the record. When the mouse pointer is moved over such a field it changes
into a shape resembling a pocket calculator. Composite fields perform a
similar function but display a textual result instead of a number. In this
case the mouse pointer changes into a large “+” sign. The latter do not,
strictly, involve “calculations” at all but because Composite and Calculated
fields work in the same way it is convenient to deal with them together. It
isn’t possible to type data directly into either of these special types of
field.

It is also possible to write your own functions in Basic and use them in
Calculated and Composite fields. The system variable TIME$ is useful in this
connection.

6.1  Calculated fields

6.1.1 Simple calculations using Numeric fields

Our first example will be to make a field containing a v.a.t. exclusive
price determine the contents of another field which includes v.a.t. at 17%.
Let the tags of these fields be VEX and VINC respectively. Click MENU over
the VINC to bring up the Field submenu and move right over  Calculations.
You will see a window with a writable icon which contains:−

VINC=

Complete the formula so that it reads:−

VINC=VEX*1.17

and click on OK or type Return. From now on the value in VINC will change
whenever you type something in VEX.  Since the v.a.t. rate is liable to
change it would be better to place the current percentage rate in another
Numeric field called VAT and make the formula attached to VINC read:−

VINC=VEX+(VEX*VAT/100)

The second example calculates the average of four fields No1, No2, No3 and
No4, placing the result in a Calculated field AVGE.  The required formula
is:−

AVGE=(No1+No2+No3+No4)/4

6.1.2 Making calculations retrospective

The default setting is such that entering or altering a formula affects only
those records added or altered after the formula entry/change. The changes
can be made retrospective by selecting the Recalculate existing records
switch on the formula entry window. On clicking OK you will be asked to
confirm that you want previous records to be made consistent with the
formula you have just entered. Changes affect the current subfile only, but
can be easily implemented in other subfiles by changing to the required
subfile, calling up the formula entry window, selecting the option button
and clicking OK.

6.1.3 Calculations using non-numeric fields (!)

You might think that the process described is only applicable to numeric
fields, but a non-numeric field may be specified in the formula − if it is
linked to a validation table. To be of use there must be  numeric data in
the column of the validation table immediately following the one to which
the field is linked (see 5.2). Suppose, for example, we have fields in a
student record for A-level exam grades, the field tags being GR1, GR2, GR3,
GR4. The grades are non-numeric but they map onto the numeric points system
which universities use to control entry.

The relationship of grades to points is as follows:−

                                    Grade   Points

                        A       10

                        B        8

                        C        6

                        D        4

                        E        2

A validation table could be set up with the grades in column 0 and the
equivalent points in column 1. Each of fields GR1−GR4 would be linked to
column 0 thus restricting input to the capital letters A-E. A further field
of Calculated type would be created to hold the points score. If we
associate this field (PTS) with the formula:−

PTS=A1+A2+A3+A4.

entering or changing the letter grades in A1-A5 will cause the points score
to be updated.

6.1.4 Calculations involving times

There is a field type, Time, which will only accept valid 24-hour times in
hh:mm:ss format.  If the tag of such a field is included in a calculation
formula it will be converted into seconds and the result used in the
calculation. Thus a Calculated field DIFF could use two Time fields, TIM1
and TIM2, in the formula:−

DIFF=TIM1-TIM2

and Powerbase would keep DIFF updated to show the difference in seconds
between the two times.

6.2  Composite fields

To enter the formula for a Composite field follow the same procedure as for
a Calculated field. Note that the relevant entry on the Field submenu now
says Combine fields. Formula entry is similar to that for Calculated fields
except that only string operations are allowed. The one most frequently used
will be “+” which allows fields to be joined together.  Suppose your
database contains fields for surname (SNAM) and forename (FNAM) and you want
to be able to print names in the format forename-surname. Define a Composite
field called NAME and attach to it the formula:−

NAME=FNAM+“ ”+SNAM

Note the quoted space separating the names.  You might want the NAME field
to show only an initial plus the surname, in which case the formula would
be:−

NAME=LEFT$(FNAM,1)+“. ”+SNAM

Composite fields, like Calculated fields, may be used in conjunction with
Time fields to perform genuine “clock arithmetic”. Referring to the example
in 6.1.4, if we attached the formula to a Composite field instead of a
Calculated one it would show the difference in hours, minutes and seconds.
The method can be extended to jobs like averaging times, e.g. with a formula
such as:−

AVE=(TIM1+TIM2+TIM3)/3

As with Calculated fields updating occurs after editing a field whose tag
appears in the attached formula. Thus NAME would be updated after changes to
FNAM or SNAM and DIFF after changes to TIM1 or TIM2. You can, however, have
Composite fields which make use of the Basic system variable TIME$. Thus a
field DAY could be linked to the formula:−

DAY=LEFT$(TIME$,3)

to make it show Mon, Tue etc. No field tags are referred to in the formula
so DAY gets updated immediately before displaying the record so that the
information is correct at that time.

6.3  User functions

These are functions, written in Basic, which accept field tags as parameters
and can be included in the calculation formulae of self-calculating fields.
All such functions must return only their principal value − RETURN variables
in the parameter list aren’t allowed. The name of each function must begin
with an upper-case “U”, e.g. FNUaverage. This is to avoid duplication of
function-names which are part of Powerbase.

All the user functions to be used by a database must be included in a Basic
program called UserFuncs which is stored in the database directory (not in
the Powerbase directory). When the database is opened UserFuncs will be
loaded as a library and Powerbase can call the user functions just as
readily as on its own functions.

The distribution disc includes a UserFuncs file containing two examples of
user functions kindly submitted by David Lenthall. Users are invited to
submit other user functions for possible inclusion in the function library.
FNUnow (which takes no parameters) operates on the value of TIME$ from the
real-time clock and returns a string containing the current date in form
DD-MM-YY. A record design could include a field of Composite type, at least
eight characters long and tagged as, for example, DATE. Associating the
field with the formula:−

DATE=FNUnow

would cause the field to display the current date at all times.

The second function, FNUageinyrs accepts two parameters, each of which
should be the tag of a field which holds a date in DD-MM-YY format, and
returns the difference between the dates to the nearest year. The first date
should be the earlier of the two. If your record design has a field for Date
of Birth (let’s call its tag DOB) you can use this function together with
FNUnow to make a Composite field (AGE) display a person’s age in years by
using the formula:−

AGE=FNUageinyrs(DOB,FNUnow)

Considerable care is needed in constructing user functions as it is very
easy to make Powerbase generate errors. In particular you must avoid giving
a function a name which is the same as a field tag − or even one which
contains a field tag as a substring. The two functions described could not,
for instance, be used in a database which had a field tagged as “now” or
“age”, although “NOW” and “AGE” could be used.

Calculated and Composite fields are normally updated when changes are made
to the fields whose tags appear in their calculation formulae. If a formula
contains a user function then the field will be updated after changes to
those fields whose tags appear in the parameter list of the function.

The previous discussion of Composite fields pointed out that they are
updated after altering fields whose tags appear in the calculation formula
or, in the case of those using TIME$, immediately before display. There is a
problem as a user functions may use TIME$ without it appearing in the
parameter list of the function  (FNUnow is an example of this). The field
AGE in the example given will only be updated when DOB is edited. It will
therefore show the age at the time you enter his/her date of birth but won’t
automatically change it a year later. To make the change automatic you could
introduce TIME$ as a parameter in FNUnow, i.e. make it FNUnow(TIME$) even
though the function doesn’t need it. When Powerbase sees TIME$ in the
formula it will update AGE. The same trick of using TIME$ as a dummy
parameter can be used for any user function associated with a field which
you want updating without having to edit the record, but note that the
record must be displayed for updating to occur. See 14.5.3 for a method of
ensuring that all records in the current subfile are updated on opening the
database.

6.4  Calculations on a column of a report

There is an option on the Print submenu called Numeric fields which is
normally shaded. It only becomes available when you highlight one or more
Numeric or Calculated fields for printing. You can then access a window
listing all the fields (if any) in the record which are of these two types.
Associated with each field are six check-boxes which can be selected to
include the following in the report:−

                        Count

                        Sum

                        Average

                        Standard deviation

                        Maximum

                        Minimum

The check-boxes are shaded until a field is included in a print selection.
They then become “live” and any or all of them may be chosen by clicking
with SELECT. The information requested is then added to the report footer
when printing takes place.